﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using SIPO.Library;
using SIPO.Library.DomainObject.Administration.Catalog;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;

namespace SIPO.Library.DataAccess.Administration.Catalog
{
   public partial class LevelCompetencyRepo
    {
       public LevelCompetency GetByCompetencyID(int CompetencyID)
       {
           LevelCompetency t = null;
           using (SqlConnection conn = new SqlConnection(connectionString))
           {
               using (SqlCommand cmd = conn.CreateCommand())
               {
                   cmd.CommandText = "SELECT ID, CompetencyID, Level, Definition, CreatedBy, CreatedDate, UpdatedDate, UpdatedBy FROM master.LevelCompetency WHERE CompetencyID = @CompetencyID";
                   cmd.CommandType = CommandType.Text;
                   cmd.Parameters.Add("@CompetencyID", SqlDbType.Int).Value = CompetencyID;
                   try
                   {
                       conn.Open();
                       using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                       {
                           while (dr.Read())
                           {
                               t = new LevelCompetency();
                               t.ID = dr[LevelCompetency.Fields.ID] == DBNull.Value ? 0 : int.Parse(dr[LevelCompetency.Fields.ID].ToString());
                               t.CompetencyID = dr[LevelCompetency.Fields.CompetencyID] == DBNull.Value ? 0 : int.Parse(dr[LevelCompetency.Fields.CompetencyID].ToString());
                               t.Level = dr[LevelCompetency.Fields.Level] == DBNull.Value ? (short?)null : short.Parse(dr[LevelCompetency.Fields.Level].ToString());
                               t.Definition = dr[LevelCompetency.Fields.Definition] == DBNull.Value ? string.Empty : dr[LevelCompetency.Fields.Definition].ToString();
                               t.CreatedBy = dr[LevelCompetency.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[LevelCompetency.Fields.CreatedBy].ToString();
                               t.CreatedDate = dr[LevelCompetency.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[LevelCompetency.Fields.CreatedDate].ToString());
                               t.UpdatedBy = dr[LevelCompetency.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[LevelCompetency.Fields.UpdatedBy].ToString();
                               t.UpdatedDate = dr[LevelCompetency.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[LevelCompetency.Fields.UpdatedDate].ToString());
                             
                           }
                       }
                   }
                   catch (Exception ex)
                   {
                       throw (ex);
                   }
                   finally
                   {
                       if (conn.State == ConnectionState.Open) conn.Close();
                   }
               }
           }
           return t;
       }

       public List<LevelCompetency> GetListByCompetencyID(int CompetencyID)
       {
           List<LevelCompetency> tt = new List<LevelCompetency>();
           using (SqlConnection conn = new SqlConnection(connectionString))
           {
               using (SqlCommand cmd = conn.CreateCommand())
               {
                   cmd.CommandText = "SELECT ID, CompetencyID, Level, Definition, CreatedBy, CreatedDate, UpdatedDate, UpdatedBy FROM master.LevelCompetency WHERE CompetencyID = @CompetencyID ORDER BY Level DESC";
                   cmd.CommandType = CommandType.Text;
                   cmd.Parameters.Add("@CompetencyID", SqlDbType.Int).Value = CompetencyID;
                   try
                   {
                       conn.Open();
                       using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                       {
                           while (dr.Read())
                           {
                               LevelCompetency t = new LevelCompetency();
                               t.ID = dr[LevelCompetency.Fields.ID] == DBNull.Value ? 0 : int.Parse(dr[LevelCompetency.Fields.ID].ToString());
                               t.CompetencyID = dr[LevelCompetency.Fields.CompetencyID] == DBNull.Value ? 0 : int.Parse(dr[LevelCompetency.Fields.CompetencyID].ToString());
                               t.Level = dr[LevelCompetency.Fields.Level] == DBNull.Value ? (short?)null : short.Parse(dr[LevelCompetency.Fields.Level].ToString());
                               t.Definition = dr[LevelCompetency.Fields.Definition] == DBNull.Value ? string.Empty : dr[LevelCompetency.Fields.Definition].ToString();
                               t.CreatedBy = dr[LevelCompetency.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[LevelCompetency.Fields.CreatedBy].ToString();
                               t.CreatedDate = dr[LevelCompetency.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[LevelCompetency.Fields.CreatedDate].ToString());
                               t.UpdatedBy = dr[LevelCompetency.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[LevelCompetency.Fields.UpdatedBy].ToString();
                               t.UpdatedDate = dr[LevelCompetency.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[LevelCompetency.Fields.UpdatedDate].ToString());
                               tt.Add(t);
                               t.Dispose();
                           }
                       }
                   }
                   catch (Exception ex)
                   {
                       throw (ex);
                   }
                   finally
                   {
                       if (conn.State == ConnectionState.Open) conn.Close();
                   }
               }
           }
           return tt;
       }		
    }
}
